package com.aluem.mydataxwebexecutor.service;

import com.aluem.mydataxwebexecutor.bean.CTable;
import com.aluem.mydataxwebexecutor.bean.Job;
import com.aluem.mydataxwebexecutor.bean.TableMsg;
import com.aluem.mydataxwebexecutor.utils.JDBCUtils;
import org.springframework.stereotype.Service;

import java.io.*;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import static ch.qos.logback.core.db.DBHelper.closeConnection;

@Service
public class TestService {

    /**
     * 将数据源数据写入mysql
     * @param jsonString
     */
    public void writeJson(String jsonString) {
        File directory = new File("src/main/resources");
        String reportPath = null;
        try {
            reportPath = directory.getCanonicalPath();
        } catch (IOException e) {
            e.printStackTrace();
        }

        System.out.println("reportPath: " + reportPath);

        String path = reportPath + "/job/1test.json";

        //TODO 存入数据库
        Job job = new Job(1,"测试",new Date(),1, "测试",path);


        Writer write = null;
        File file = new File(path);
        System.out.println("file.getPath():" + file.getPath());
        try {
            write = new OutputStreamWriter(new FileOutputStream(file), "UTF-8");
            System.out.println("json文件内容：" + jsonString);
            write.write(jsonString);
            System.out.println("文件创建成功！");
            write.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (write != null) {
                    write.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }


    //创建数据库表
    public void createDataBase(TableMsg tableMsg) {

        //获取带创建表的信息
        List<CTable> createTables = getCreateTableCloumnMsg(tableMsg);

        //生成sql语句
        String sql = splicStringSql(tableMsg, createTables);
        System.out.println("sql:" + sql);

        Connection conn = null;

        //注意：创建数据表的数据库信息不是读取的数据库信息，而是写入的数据库信息
        try {
            //TODO 可以使用mybatis实现
            TableMsg writeTabMsg = new TableMsg();
            writeTabMsg.setJdbcUrl("jdbc:mysql://192.168.200.129/test");
            writeTabMsg.setUsername("root");
            writeTabMsg.setPassword("ainiyo");
            conn = JDBCUtils.getConnection(writeTabMsg);
            //创建数据库表
            JDBCUtils.update(conn, sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null, null);
        }

    }

    //根据用户选择的列名，获取待创建数据库表的列的列名、类型和长度
    private static List<CTable> getCreateTableCloumnMsg(TableMsg tableMsg) {
        //要创建的列名
        List<String> columns = tableMsg.getColumns();

        //所有列名
        List<CTable> allCloumnMsg = getALLCloumnMsg(tableMsg);

        //存放待创建的列信息
        List<CTable> createCloumnMsg = new ArrayList<>();

        if ("*".equals(tableMsg.getColumns().get(0))) { //要创建的列即所有列
            createCloumnMsg = allCloumnMsg;
        } else {
            for (CTable cTable : allCloumnMsg) {
                for (String column : columns) {
                    if (column.equalsIgnoreCase(cTable.getColumnName())) { //该列需要创建
                        createCloumnMsg.add(cTable);
                    }
                }
            }
        }


        System.out.println("待创建的列的信息：" + createCloumnMsg);
        return createCloumnMsg;
    }



    //获取读取数据库表的所有列的列名、类型、长度
    public static List<CTable> getALLCloumnMsg(TableMsg tableMsg) {
        if ("oracle".equalsIgnoreCase(tableMsg.getDatabase())) { //读取的数据库为oracle数据库

            Connection conn = null;

            System.out.println(tableMsg);

            try {
                //获取连接
                conn = JDBCUtils.getConnection(tableMsg);
                //获取要转移的列名的类型和长度
                List<String> columnNames = JDBCUtils.getColumnNames(tableMsg.getTable(), conn);
                List<String> columnTypes = JDBCUtils.getColumnTypes(tableMsg.getTable(), conn);
                List<Integer> columnLength = JDBCUtils.getColumnLength(tableMsg.getTable(), conn);

//                System.out.println("=============>" + columnNames);
//                System.out.println("columnLength=====>" + columnLength);
                //oracle数据类型转为mysql数据类型
                oracleTypeToMysqlType(columnTypes, columnLength);

//                System.out.println("columnTypes============>" + columnTypes);
//                System.out.println("columnLength============>" + columnLength);
                //封装并返回
                List<CTable> cTables = new ArrayList<>();

                for (int i = 0; i < columnLength.size(); i++) {
                    CTable cTable = new CTable(columnNames.get(i), columnTypes.get(i), columnLength.get(i));
                    cTables.add(cTable);
                }
                return cTables;


            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                //关闭资源
                if (conn != null) {
                    try {
                        conn.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }

        } else if ("mysql".equalsIgnoreCase(tableMsg.getDatabase())) { //读取的数据库为mysql数据库

            Connection conn = null;

            try {
                //获取数据库连接
                conn = JDBCUtils.getConnection(tableMsg);
                //获取要转移的列名的类型和长度
                List<String> columnNames = JDBCUtils.getColumnNames(tableMsg.getTable(), conn);
                List<String> columnTypes = JDBCUtils.getColumnTypes(tableMsg.getTable(), conn);
                List<Integer> columnLength = JDBCUtils.getColumnLength(tableMsg.getTable(), conn);

                List<CTable> cTables = new ArrayList<>();

                for (int i = 0; i < columnLength.size(); i++) {
                    CTable cTable = new CTable(columnNames.get(i), columnTypes.get(i), columnLength.get(i));
                    cTables.add(cTable);
                }
                return cTables;
                //System.out.println(cTables);

            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                //关闭数据库连接
                if (conn != null) {
                    closeConnection(conn);
                }
            }


        } else {
            throw new RuntimeException("暂时不支持该类型数据库");
        }
        return null;
    }




    //根据需要的列名信息和表信息，连接sql语句
    private String splicStringSql(TableMsg tableMsg, List<CTable> createTables) {
        String preSql = "create table " + tableMsg.getTable() + "(";

        int i = 0;
        int size = createTables.size();
        for (CTable createTable : createTables) {
            preSql = preSql + createTable.getColumnName().toLowerCase() + " " + createTable.getColumnType().toLowerCase() +
                    "(" + createTable.getColumnLength() + ")";
            if (i != size - 1) {
                preSql += ",";
            }
            i++;
        }

        String sql = preSql + ")";

        return sql;
    }


    //根据列名拼接字符串
    private String splicString(ArrayList<String> columns) {
        String columnsString = "";
        int i = 0; //判断是否到最后一个
        int size = columns.size();
        //不为空则拼接列名字符串，为空则为*
        if (columns != null) {
            for (String column : columns) {

                if (i == size || i== 0) {
                    columnsString += column;
                } else {
                    columnsString = columnsString + "," + column;
                }
                i++;
            }
        } else {
            columnsString = "*";
        }
        return columnsString;
    }



    //oracle数据类型转为mysql数据类型(长度做相应改变)
    private static void oracleTypeToMysqlType(List<String> columnTypes, List<Integer> columnLengths) {
        for (int i = 0; i < columnTypes.size(); i++) {
            if ("VARCHAR2".equalsIgnoreCase(columnTypes.get(i))) {
                columnTypes.set(i, "VARCHAR");
            } else if ("NUMBER(3,0)".equalsIgnoreCase(columnTypes.get(i))) {
                columnTypes.set(i, "TINYINT");
            } else if ("NUMBER(5,0)".equalsIgnoreCase(columnTypes.get(i))) {
                columnTypes.set(i, "SMALLINT");
            } else if ("NUMBER(7,0)".equalsIgnoreCase(columnTypes.get(i))) {
                columnTypes.set(i, "MEDUIMINT");
            } else if ("NUMBER(10,0)".equalsIgnoreCase(columnTypes.get(i))) {
                columnTypes.set(i, "INT");
            } else if ("NUMBER(20,0)".equalsIgnoreCase(columnTypes.get(i))) {
                columnTypes.set(i, "BIGINT");
            } else if ("NUMBER".equalsIgnoreCase(columnTypes.get(i))) {
                columnTypes.set(i, "DECIMAL");
            } else if ("BINARY_FLOAT".equalsIgnoreCase(columnTypes.get(i))) {
                columnTypes.set(i, "FLOAT");
            } else if ("FLOAT".equalsIgnoreCase(columnTypes.get(i))) {
                columnTypes.set(i, "FLOAT");
            } else if ("REAL".equalsIgnoreCase(columnTypes.get(i))) {
                columnTypes.set(i, "FLOAT");
            } else if ("BINARY_DOUBLE".equalsIgnoreCase(columnTypes.get(i))) {
                columnTypes.set(i, "DOUBLE");
            } else if ("PRECISION ".equalsIgnoreCase(columnTypes.get(i))) {
                columnTypes.set(i, "DOUBLE");
            } else if ("DOUBLE ".equalsIgnoreCase(columnTypes.get(i))) {
                columnTypes.set(i, "DOUBLE");
            } else if ("DATE ".equalsIgnoreCase(columnTypes.get(i))) {
                columnTypes.set(i, "DATETIME");
            } else if ("CHAR ".equalsIgnoreCase(columnTypes.get(i))) {
                columnTypes.set(i, "CHAR");
                if (columnLengths.get(i) > 255) {
                    columnLengths.set(i, 255);
                }
            } else if ("CLOB ".equalsIgnoreCase(columnTypes.get(i))) {
                columnTypes.set(i, "LONGTEXT");
            } else if ("BLOB ".equalsIgnoreCase(columnTypes.get(i))) {////////////////////
                columnTypes.set(i, "LONGBLOB");
            } else if ("RAW ".equalsIgnoreCase(columnTypes.get(i))) {
                columnTypes.set(i, "VARBINARY");
            } else if ("CLOB ".equalsIgnoreCase(columnTypes.get(i))) {
                columnTypes.set(i, "LONGTEXT");
            } else if ("CLOB ".equalsIgnoreCase(columnTypes.get(i))) {
                columnTypes.set(i, "LONGTEXT");
            }
        }
    }


}
